# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：stock_info.py
#日期：2020-08-11
#备注：东方财富通获取股票核心题材信息、股票基本信息、股票股东信息、股票利润信息、主营构成分析
CREATE TABLE `stock_business` (
  `business_stNo` varchar(20) NOT NULL COMMENT '股票代码',
  `business_date` varchar(20) NOT NULL COMMENT '统计日期',
  `report_hy` varchar(1000) NOT NULL COMMENT '按行业统计',
  `report_qy` varchar(1000) NOT NULL COMMENT '按区域统计',
  `report_cp` varchar(1000) NOT NULL COMMENT '按产品统计',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`business_stNo`,`business_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='股票主营构成分析统计信息';
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import requests
import threading
import time
import re
from pacong.stock.stock_config import db,cursor,headers
from pycacho.cachobase.logger import Logger


class stockInfo(object):
    def __init__(self):
        self.logger = Logger("stockInfo").get_log()
        self.headers = headers
        self.db = db
        self.cursor = cursor
        self.lock = threading.Lock()


    def create_table(self):
        sql1 = 'drop table if exists stock_coreInfo,stock_gdInfo;'    #stock_baseInfo,stock_gdInfo,stock_profitInfo
        #sql2 = 'CREATE TABLE stock_baseInfo (st_no varchar(10),st_name varchar(10),st_company varchar(100),st_type varchar(30),st_industry varchar(100),st_CSRCIndustry varchar(100),st_foundDate varchar(50),st_marketDate varchar(50),st_releaseWay varchar(50),st_releaseNum varchar(50),st_releasePE float,st_releasePrice float,st_openPrice float,st_closePrice float,st_baseInfo varchar(3000),st_manageInfo varchar(3000),' \
               #'PRIMARY KEY (st_no));'               # 股票基本信息
        #sql3 = 'CREATE TABLE stock_profitInfo (pr_stNo varchar(10),pr_operationIncome float,pr_netProfits float,pr_kfProfits float,pr_circulationStock float,pr_totalStock float,pr_netAsset float,pr_commonFund float,pr_unProfit float,pr_baseIncome float,pr_xsIncome float,pr_netAssetRate float,pr_grossProfitRate float,pr_assetLiabilityRate float,pr_time varchar(10),' \
               #'PRIMARY KEY (pr_stNo,pr_time));'     # 股票收益情况
        sql4 = 'CREATE TABLE stock_gdInfo (gd_stNo varchar(10),gd_date varchar(20),gd_num float,gd_numLast float,gd_perNum float,gd_perNumLast float,gd_price float,gd_perMoney float,gd_total10 float,gd_market10 float,' \
               'PRIMARY KEY (gd_stNo,gd_date));'     # 股东统计情况
        sql5 = 'CREATE TABLE stock_coreInfo (core_stNo varchar(10),core_theme varchar(50),core_detail varchar(1000),' \
               'PRIMARY KEY (core_stNo,core_theme));'             # 股票核心题材信息
        self.cursor.execute(sql1)
        #self.cursor.execute(sql2)
       # self.cursor.execute(sql3)
        self.cursor.execute(sql4)
        self.cursor.execute(sql5)
        self.db.commit()


    def get_stockNo(self):
        sql = 'select stock_type,stock_no from china_stock;'
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        stocks = []
        for stock in results:
            stock_type = stock[0]
            stock_no = stock[1]
            if stock_type == '1':
                stock_no = 'SH' + stock_no
            else:
                stock_no = 'SZ' + stock_no
            stocks.append(stock_no)
        return stocks


    # 公司概况：获取股票基本资料（公司介绍、上市时信息）
    def get_CompanySurvey(self,stock_no):
        info_url = 'http://emweb.securities.eastmoney.com/CompanySurvey/CompanySurveyAjax?code={0}'.format(stock_no)
        resp = requests.get(info_url, headers=self.headers)
        data = resp.json()
        self.logger.info("股票基本资料链接为：%s", info_url)
        st_no = data['jbzl']['agdm']  # 股票代码
        st_name = data['jbzl']['agjc']  # 股票简称
        st_company = data['jbzl']['gsmc']  # 公司名称
        st_type = data['jbzl']['zqlb']  # 证券类别（深交所，上交所）
        st_industry = data['jbzl']['sshy']  # 所属行业
        st_CSRCIndustry = data['jbzl']['sszjhhy']  # 所属证监会行业
        st_baseInfo = data['jbzl']['gsjj'].replace("\'", '')  # 公司简介
        st_manageInfo = data['jbzl']['jyfw'].replace("\'", '')  # 经营范围
        st_foundDate = data['fxxg']['clrq']  # 公司成立日期
        st_marketDate = data['fxxg']['ssrq']  # 上市日期
        st_releaseWay = data['fxxg']['fxfs']  # 发行方式
        st_releaseNum = data['fxxg']['fxl']  # 发行量
        if data['fxxg']['srspj'] not in ['-', '--']:
            st_releasePE = float(data['fxxg']['srspj'])  # 发行市盈率
        else:
            st_releasePE = 0
        if data['fxxg']['mgfxj'] not in ['-', '--']:
            st_releasePrice = float(data['fxxg']['mgfxj'])  # 发行价
        else:
            st_releasePrice = 0
        if data['fxxg']['srkpj'] not in ['-', '--']:
            st_openPrice = float(data['fxxg']['srkpj'])  # 首日开盘价
        else:
            st_openPrice = 0
        if data['fxxg']['srspj'] not in ['-', '--']:
            st_closePrice = float(data['fxxg']['srspj'])  # 首日收盘价
        else:
            st_closePrice = 0
        self.lock.acquire()
        self.baseInfo_to_mysql(st_no, st_name, st_company, st_type, st_industry, st_CSRCIndustry, st_foundDate,
                          st_marketDate,st_releaseWay, st_releaseNum,
                          st_releasePE, st_releasePrice, st_openPrice, st_closePrice, st_baseInfo, st_manageInfo)
        self.lock.release()


    # 将股票基本信息（股票公司基本信息，上市信息）
    def baseInfo_to_mysql(self,st_no, st_name, st_company, st_type, st_industry, st_CSRCIndustry, st_foundDate,
                          st_marketDate,st_releaseWay,st_releaseNum, st_releasePE, st_releasePrice,
                          st_openPrice, st_closePrice, st_baseInfo,st_manageInfo):
        sql = "insert into stock_baseInfo (st_no,st_name,st_company,st_type,st_industry,st_CSRCIndustry,st_foundDate,st_marketDate," \
              "st_releaseWay,st_releaseNum,st_releasePE,st_releasePrice,st_openPrice,st_closePrice,st_baseInfo,st_manageInfo) " \
              "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%f','%f','%f','%f','%s','%s');" \
              % (
                  st_no, st_name, st_company, st_type, st_industry, st_CSRCIndustry, st_foundDate, st_marketDate,
                  st_releaseWay,
                  st_releaseNum, st_releasePE, st_releasePrice, st_openPrice, st_closePrice, st_baseInfo, st_manageInfo)
        try:
            self.cursor.execute(sql)
        except Exception as e:
            # 发生错误时回滚
            self.db.rollback()
            print(str(e))
        else:
            self.db.commit()  # 事务提交
            self.logger.info('%s股票基本信息插入成功！',st_no)


    # 公司概况：获取股票基本资料（公司介绍、上市时信息）
    def get_OperationsRequired(self, stock_no):
        info_url = 'http://emweb.securities.eastmoney.com/OperationsRequired/OperationsRequiredAjax?times=1&code={0}'.format(stock_no)
        resp = requests.get(info_url, headers=self.headers)
        data = resp.json()
        self.logger.info("股票操盘必读链接为：%s", info_url)
        pr_stNo = stock_no[2:]
        profit1 = data['zxzb1']
        # print(profit1)   # 列表数据，调试用
        profit_info =re.findall(r'class="tips-data-Left"><span>(\d{,1}-{,2}.{,10}\d{,4})</span>',profit1)
        # print(profit_info)  # 列表数据，调试用
        if profit_info[0] == '--':
            pr_baseIncome = 0.0
        else:
            pr_baseIncome = float(profit_info[0])                             # 基本每股收益(元)
        #pr_kfIncome = float(profit_info[3])                                 # 扣非每股收益(元) 有空值，不记录
        if profit_info[6] == '--':
            pr_xsIncome = 0.0
        else:
            pr_xsIncome = float(profit_info[6])                              # 稀释每股收益(元)
        pr_netAsset = float(profit_info[1])                                  # 每股净资产(元)
        pr_commonFund = float(profit_info[4])                                 # 每股公积金(元)
        pr_unProfit = float(profit_info[7])                                   # 每股未分配利润(元)
        if profit_info[8] == '--':
            pr_circulationStock = 0.0
        else:
            pr_circulationStock = float(profit_info[8].replace(',',''))*10000     # 流通股本(股)
        pr_totalStock = float(profit_info[5].replace(',',''))*10000           # 总股本(股)
        pr_time = re.findall(r'<div class="tfoot">数据来源:(\d.*);其中',profit1)[0]
        profit2 = data['zxzb2']
        # print(profit2)      # 列表数据，调试用
        profit2_info = re.findall(r'</th><td class="tips-data-Left"><span>(\d{,1}-{,2}.{,10}\d{,4})</span>',profit2)
        # print(profit2_info)  # 列表数据，调试用
        if profit2_info[3][-1:] == '亿':
            pr_operationIncome = float(profit2_info[3][0:-1])*100000000         # 营业总收入(元)866.64亿
        elif profit2_info[3][-1:] == '-':
            pr_operationIncome = 0.0
        else:
            pr_operationIncome = float(profit2_info[3][0:-1]) * 10000
        if profit2_info[6][-1:] == '亿':
            pr_netProfits = float(profit2_info[6][0:-1])*100000000              # 归属净利润(元) 3226.16万
        else:
            pr_netProfits = float(profit2_info[6][0:-1]) * 10000
        if profit2_info[9][-1:] == '亿':
            pr_kfProfits = float(profit2_info[9][0:-1]) * 100000000             # 扣非净利润(元)
        else:
            pr_kfProfits = float(profit2_info[9][0:-1]) * 10000
        if profit2_info[0] == '--':
            pr_netAssetRate = 0
        else:
            pr_netAssetRate = float(profit2_info[0])                           # 加权净资产收益率(%)
        if profit2_info[1] == '--':
            pr_grossProfitRate = 0
        else:
            pr_grossProfitRate = float(profit2_info[1])                     # 毛利率(%)
        pr_assetLiabilityRate = float(profit2_info[2])                      # 资产负债率(%)
        self.lock.acquire()
        self.profitInfo_to_mysql(pr_stNo,pr_operationIncome,pr_netProfits,pr_kfProfits,pr_circulationStock,pr_totalStock,pr_netAsset,pr_commonFund,pr_unProfit,pr_baseIncome,pr_xsIncome,pr_netAssetRate,pr_grossProfitRate,pr_assetLiabilityRate,pr_time)
        self.lock.release()

        # 股东情况
        shareholder = data['gdrs']
        # print(type(shareholder), shareholder)                # 列表数据，调试用
        for share in shareholder:
            gd_date = share ['rq']                              # 季度股东统计（时间）
            if share['gdrs'][-1:] == '万':
                gd_num = float(share['gdrs'][0:-1])*10000        # 股东数量（股）万或者5800
            else:
                gd_num = float(share['gdrs'])
            if share['gdrs_jsqbh'] == '--':
                gd_numLast = 0.0
            else:
                gd_numLast = share['gdrs_jsqbh']                    # 股东数量对比上一期（%）
            if share['rjltg'][-1:] == '万':
                gd_perNum = float(share['rjltg'][0:-1])*10000       # 人均流通股（万）
            else:
                gd_perNum = float(share['rjltg'])
            if share['rjltg_jsqbh'] == '--':
                gd_perNumLast = 0.0
            else:
                gd_perNumLast = share['rjltg_jsqbh']                # 人均流通股对比上一期（%）
            gd_price = share['gj']                                 # 人均持有股价（元）
            if share['rjcgje'][-1:] == '万':
                gd_perMoney = float(share['rjcgje'][0:-1])*10000  # 人均持股金额(元)-万
            elif share['rjcgje'] == '--':
                gd_perMoney = 0.0
            else:
                gd_perMoney = float(share['rjcgje'])
            if share['qsdgdcghj'] == '--':
                gd_total10 = 0.0                                # 前十大股东持股合计(%)
            else:
                gd_total10 = float(share['qsdgdcghj'])
            if share['qsdltgdcghj'] == '--':
                gd_market10 = 0.0
            else:
                gd_market10 = float(share['qsdltgdcghj'])       # 前十大流通股东持股合计(%)
            self.lock.acquire()
            self.gdInfo_to_mysql(pr_stNo,gd_date,gd_num,gd_numLast,gd_perNum,gd_perNumLast,gd_price,gd_perMoney,gd_total10,gd_market10)
            self.lock.release()


    # 将股票盈利信息记录
    def profitInfo_to_mysql(self,pr_stNo,pr_operationIncome,pr_netProfits,pr_kfProfits,pr_circulationStock,pr_totalStock,pr_netAsset,pr_commonFund,pr_unProfit,pr_baseIncome,pr_xsIncome,pr_netAssetRate,pr_grossProfitRate,pr_assetLiabilityRate,pr_time):
        sql = "insert into stock_profitInfo (pr_stNo,pr_operationIncome,pr_netProfits,pr_kfProfits,pr_circulationStock,pr_totalStock,pr_netAsset,pr_commonFund,pr_unProfit,pr_baseIncome,pr_xsIncome,pr_netAssetRate,pr_grossProfitRate,pr_assetLiabilityRate,pr_time) " \
              "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');"\
              % (pr_stNo,pr_operationIncome,pr_netProfits,pr_kfProfits,pr_circulationStock,pr_totalStock,pr_netAsset,pr_commonFund,pr_unProfit,pr_baseIncome,pr_xsIncome,pr_netAssetRate,pr_grossProfitRate,pr_assetLiabilityRate,pr_time)
        try:
            self.cursor.execute(sql)
        except Exception as e:
            self.db.rollback()
            print(str(e))
        else:
            self.db.commit()
            self.logger.info('%s股票盈利信息插入成功！',pr_stNo)


    # 将股东统计信息
    def gdInfo_to_mysql(self,gd_stNo,gd_date,gd_num,gd_numLast,gd_perNum,gd_perNumLast,gd_price,gd_perMoney,gd_total10,gd_market10):
        sql = "insert into stock_gdInfo (gd_stNo,gd_date,gd_num,gd_numLast,gd_perNum,gd_perNumLast,gd_price,gd_perMoney,gd_total10,gd_market10) " \
              "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');"\
              % (gd_stNo,gd_date,gd_num,gd_numLast,gd_perNum,gd_perNumLast,gd_price,gd_perMoney,gd_total10,gd_market10)
        try:
            self.cursor.execute(sql)
        except Exception as e:
            self.db.rollback()
            print(str(e))
        else:
            self.db.commit()
            self.logger.info('%s股东统计信息插入成功!',gd_stNo)


    # 公司核心题材：（行业板块、概念板块）
    def get_CoreConception(self, stock_no):
        info_url = 'https://emweb.securities.eastmoney.com/PC_HSF10/CoreConception/PageAjax?code={0}'.format(stock_no)
        resp = requests.get(info_url, headers=self.headers)
        data = resp.json()
        self.logger.info("股票公司核心题材链接为：%s", info_url)
        core_stNo = stock_no[2:]
        board_info = data['ssbk']
        board_name = '所属板块'
        info = ''
        for board in board_info:
            info = info + board['BOARD_NAME'] + ' '
        #print(core_stNo, board_name, info)
        with self.lock:
            self.coreInfo_to_mysql(core_stNo, board_name, info)
        core_info = data['hxtc']
        for core in core_info:
            # print(core)
            core_theme = core['KEYWORD']
            core_detail = core['MAINPOINT_CONTENT']
            with self.lock:
                #print(core_stNo,core_theme,core_detail)
                 self.coreInfo_to_mysql(core_stNo,core_theme,core_detail)


    # 将股票核心题材信息插入数据库
    def coreInfo_to_mysql(self,core_stNo,core_theme,core_detail):
        sql = "insert into stock_coreInfo (core_stNo,core_theme,core_detail) values ('%s','%s','%s');" \
              %(core_stNo,core_theme,core_detail)
        try:
            self.cursor.execute(sql)
        except Exception as e:
            self.db.rollback()
            print(str(e))
        else:
            self.db.commit()
            self.logger.info('%s股票核心题材信息插入成功!', core_stNo)

    # 公司主营构成分析
    def business_analysis(self, stock_no):
        info_url = 'http://f10.eastmoney.com/BusinessAnalysis/BusinessAnalysisAjax?code={0}'.format(stock_no)
        resp = requests.get(info_url, headers=self.headers)
        data = resp.json()
        self.logger.info("股票主营构成分析链接为：%s", info_url)
        business_info = data['zygcfx']
        print(business_info)
        business_stNo = stock_no[2:]
        for info in business_info:
            try:
                bu_rq = info['rq']       # 报告期
                bu_hy = info['hy']      # 按行业划分
                if bu_hy==[]:
                    bu_hy = ''
                bu_qy = info['qy']       # 按区域划分
                if bu_qy==[]:
                    bu_qy = ''
                bu_cp = info['cp']       # 按产品划分
                if bu_cp==[]:
                    bu_cp = ''
                print(bu_hy)
                print(bu_qy)
                self.business_to_mysql(business_stNo,bu_rq,bu_hy,bu_qy,bu_cp)
            except:
                pass

    # 将股票主营构成分析信息插入数据库
    def business_to_mysql(self,business_stNo,bu_rq,bu_hy,bu_qy,bu_cp):
        sql = "insert into stock_business (business_stNo,business_date,report_hy,report_qy,report_cp,create_time) values ('%s','%s','%s','%s','%s',now());" \
              %(business_stNo,bu_rq,bu_hy,bu_qy,bu_cp)
        try:
            self.cursor.execute(sql)
        except Exception as e:
            self.db.rollback()
            print(str(e))
        else:
            self.db.commit()
            self.logger.info('%s主营构成分析信息插入成功!', business_stNo)


    def main(self):
        #self.create_table()
        stocks = self.get_stockNo()
        for stock_no in stocks:
            #th1 = threading.Thread(target=self.get_CompanySurvey,args=(stock_no,))
            th2 = threading.Thread(target=self.get_OperationsRequired, args=(stock_no,))
            th3 = threading.Thread(target=self.get_CoreConception, args=(stock_no,))   # 核心概念
            #th4 = threading.Thread(target=self.business_analysis, args=(stock_no,))

            #th1.start()
            th2.start()
            th3.start()
            #th4.start()
            #th1.join()
            th2.join()
            th3.join()
            #th4.join()


if __name__ == '__main__':
    t1 = time.time()
    stock = stockInfo()
    stock.create_table()    # 调试用
    stock.main()
    #stock.get_CoreConception('SZ000010')   # 调试用
    #stock.business_analysis('SZ300001')
    stock.logger.info("总共耗时：%.2f秒 ", time.time() - t1)